﻿Imports System.Data
Imports System.Data.SqlClient
Public Class CarroDAL
#Region "Incluir"
    Public Function Inserir(ByVal Descricao As String, ByVal CodStatus As Integer, Modelo As String, CodMarca As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try

            cmd.CommandText = "insert into Vendas.Ofi_Carro(crr_descricao,sta_codigo,crr_modelo,mar_codigo) values (@descricao,@status,@modelo,@codmarca)"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = CodStatus
            cmd.Parameters.Add(New SqlParameter("@modelo", SqlDbType.VarChar)).Value = Modelo
            cmd.Parameters.Add(New SqlParameter("@codmarca", SqlDbType.Int)).Value = CodMarca
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region

#Region "Alterar"
    Public Function Alterar(ByVal Descricao As String, ByVal Status As Integer, ByVal Codigo As Integer, Modelo As String, CodMarca As Integer) As Integer
        Dim cod As Integer
        Dim cmd = New SqlCommand
        Try

            cmd.CommandText = "update Vendas.Ofi_Carro set crr_descricao=@descricao, sta_codigo=@status,crr_modelo=@modelo,mar_codigo=@codmarca where crr_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = Status
            cmd.Parameters.Add(New SqlParameter("@modelo", SqlDbType.VarChar)).Value = Modelo
            cmd.Parameters.Add(New SqlParameter("@codmarca", SqlDbType.Int)).Value = CodMarca
            cod = objconexao.executaCmd(cmd)

        Catch ex As Exception
            Throw ex

        Finally
            cmd = Nothing

        End Try
        Return Codigo
    End Function
#End Region

#Region "Deletar"
    Public Function Deletar(ByVal codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim ret As Integer

        Try

            cmd.CommandText = "update Vendas.Ofi_Carro set  sta_codigo=3 where crr_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region

#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0
                    cmd.CommandText = "select * from vw_VenCarro where crr_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1
                    cmd.CommandText = "select * from vw_VenCarro where crr_descricao COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2
                    cmd.CommandText = "select * from vw_VenCarro where crr_modelo COLLATE SQL_Latin1_General_CP1_CI_AI  like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 3
                    cmd.CommandText = "select * from vw_VenCarro where sta_descricao = @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
                Case Else
                    cmd.CommandText = "select * from vw_VenCarro where sta_codigo < 3 order by crr_descricao"
            End Select

            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

#Region "Valida Replica"
    Function ValidaReplica(Codigo As Integer, Descricao As String, Modelo As String) As Boolean
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_VenCarro where crr_descricao=@descricao and crr_modelo=@modelo and crr_codigo<>@codigo and sta_codigo <>3"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = Descricao
            cmd.Parameters.Add(New SqlParameter("@modelo", SqlDbType.VarChar)).Value = Modelo
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False

            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region

#Region "Valida exclusão"
    Public Function ValidaExcluir(ByVal Codigo As Integer, Status As Integer) As Boolean
        Dim cmd = New SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            If Status <> 1 Then
                cmd.CommandText = "select*from Vendas.Ofi_Placa p inner join Vendas.Ofi_Carro c on p.crr_codigo=c.crr_codigo where p.sta_codigo <> 3 and c.crr_codigo=@codigo and c.sta_codigo <> 3 "
                cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Codigo
                tab = objconexao.executaConsulta(cmd)
                If (tab.Rows.Count > 0) Then
                    ret = False
                End If
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region
End Class
